package com.sms.project;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.sms.DB.ConnectDB;
import com.sms.DB.OperateDB;

public class Task {
	/*
	 * 获得老师权限下能够看到的所有项目 param:老师的编号
	 */
	public static List<Project> getTProject(String userno, String condition) {
		String append = "";
		if (condition.trim().equals("finish")) {
			append = "and status = '1'";
		} else if (condition.trim().equals("notfinish")) {
			append = "and status = '0'";
		} else {
			append = "";
		}
		String sql = "select * from taskinfo where fromid = '" + userno + "' "
				+ " and task_type = '项目任务' " + append + "order by taskid desc";
		// System.out.println(sql);
		List<Project> plist = null;
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化plist对象
			plist = new ArrayList<Project>();
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				Project p = new Project();
				p.init(map);
				plist.add(p);
			}
		}
		return plist;
	}

	// 获得指定项目的发给指定用户的汇报(跟帖)
	public static List<PReport> getPReport(int taskID, String toID) {
		String sql = "select tr.* from taskreport as tr, taskmember as tm "
				+ " where tr.taskid = " + taskID
				+ " and tr.t_reportid = tm.taskid " + " and ((fromid = '"
				+ toID + "' ) or ( memberid = '" + toID + "'))"
				+ " and pid = 0 order by t_reportid desc";
		// System.out.println(sql);
		List<PReport> prlist = new ArrayList<PReport>();
		List<Map> list = new ArrayList<Map>();
		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PReport pr = new PReport();
				pr.init(map, 0);
				prlist.add(pr);
			}
		}

		return prlist;
	}

	// 获得指定项目的发给指定条数的汇报的(跟帖)
	public static List<PReport> getPReport(int taskID, String toID,
			int startPos, int pageSize) {
		String sql = "select tr.* from taskreport as tr, taskmember as tm "
				+ " where tr.taskid = " + taskID
				+ " and tr.t_reportid = tm.taskid " + " and ((fromid = '"
				+ toID + "' ) or ( memberid = '" + toID + "'))"
				+ " and pid = 0 group by t_reportid"
				+ " order by t_reportid desc " + " limit " + startPos + ", "
				+ pageSize;
		System.out.println(sql);
		List<PReport> prlist = new ArrayList<PReport>();
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化prlist对象
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PReport pr = new PReport();
				pr.init(map, 0);
				prlist.add(pr);
			}
		}
		return prlist;
	}

	// 获得指定项目的其他可见汇报(跟帖)
	public static List<PReport> getOtherPReport(int taskID, String toID) {
		String sql = "select * from taskreport " + " where taskid = " + taskID
				+ " and fromid != '" + toID + "' " + " and pid = 0 "
				+ " and tr_type = '0' " + " and t_reportid not in("
				+ " select taskid from taskmember where memberid = '" + toID
				+ "') " + " order by t_reportid desc ";

		System.out.println(sql);
		List<PReport> prlist = new ArrayList<PReport>();
		List<Map> list = new ArrayList<Map>();
		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PReport pr = new PReport();
				pr.init(map, 0);
				prlist.add(pr);
			}
		}

		return prlist;
	}

	// 获得指定项目的发给指定条数的汇报的(跟帖)
	public static List<PReport> getOtherPReport(int taskID, String toID,
			int startPos, int pageSize) {
		String sql = "select * from taskreport " + " where taskid = " + taskID
				+ " and fromid != '" + toID + "' " + " and pid = 0 "
				+ " and tr_type = '0' " + " and t_reportid not in("
				+ " select taskid from taskmember where memberid = '" + toID
				+ "') " + " order by t_reportid desc limit " + startPos
				+ ", " + pageSize;

		// System.out.println(sql);
		List<PReport> prlist = new ArrayList<PReport>();
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化prlist对象
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PReport pr = new PReport();
				pr.init(map, 0);
				prlist.add(pr);
			}
		}
		return prlist;
	}

	public static List<PReport> getReplyReport(PReport pr, int grade) {
		List<PReport> prrlist = new ArrayList<PReport>();
		List<Map> list = new ArrayList<Map>();
		String sql = "";

		sql = "select * from taskreport where pid = " + pr.getPrID();
		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化prrlist对象
			prrlist = new ArrayList<PReport>();
			grade += 1;
			// System.out.println("grade" + grade);
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PReport prr = new PReport();
				prr.init(map, grade);
				prrlist.add(prr);
				if (prr.getIsleaf().equals("1")) {
					prrlist.addAll(getReplyReport(prr, grade));
				}
			}
		}
		return prrlist;
	}

	/*
	 * 删除指定id的项目汇报
	 */
	public static boolean delReport(String delID) {
		boolean isSuccess = true;
			int delid = Integer.parseInt(delID);

			// 删除汇报的附件以及成员
			String sql = "select * from taskreport where t_reportid = "
					+ delid;
			List<Map> list = new ArrayList<Map>();
			list = OperateDB.executeQuery(sql);
			if (list.size() > 0) {// 存在该汇报
				Map map = list.get(0);
				String isAppend = map.get("isappend").toString();

				if (isAppend.equals("1")) {// 有附件,则删除附件
					sql = "delete from appendinfo where append_type = '任务报告'"
							+ "and fileid = " + delid;

					int n = OperateDB.update(sql);
					if (n == 0) {// 删除附件失败
						isSuccess = false;
					}
				}

				// 删除taskmember中的信息
				if (isSuccess) {
					sql = "delete from taskmember where taskid = " + delid;
					int tm = OperateDB.update(sql);
					if (tm == 0) {// 删除失败
						isSuccess = false;
					}
				}

				// 删除该汇报
				if (isSuccess) {
					sql = "delete from taskreport where t_reportid = "
							+ delid;
					int n = OperateDB.update(sql);
					if (n == 0) {// 删除失败
						isSuccess = false;
					}
				}

		} else {// 不存在汇报
			isSuccess = false;
		}
		return isSuccess;
	}
		

	/*
	 * 删除指定id的汇报
	 */
	public static boolean delReport(String[] delID) {
		boolean isSuccess = true;
		if (delID.length < 1) {
			isSuccess = false;
		} else {
			for (int i = 0; i < delID.length; i++) {
				// System.out.println(delID[i]);
				int delid = Integer.parseInt(delID[i]);

				// 删除汇报的附件以及成员
				String sql = "select * from taskreport where t_reportid = "
						+ delid;
				List<Map> list = new ArrayList<Map>();
				list = OperateDB.executeQuery(sql);
				if (list.size() > 0) {// 存在该汇报
					Map map = list.get(0);
					String isAppend = map.get("isappend").toString();

					if (isAppend.equals("1")) {// 有附件,则删除附件
						sql = "delete from appendinfo where append_type = '任务报告'"
								+ "and fileid = " + delid;

						int n = OperateDB.update(sql);
						if (n == 0) {// 删除附件失败
							isSuccess = false;
							break; // 跳出循环
						}
					}

					// 删除taskmember中的信息
					if (isSuccess) {
						sql = "delete from taskmember where taskid = " + delid;
						int tm = OperateDB.update(sql);
						if (tm == 0) {// 删除失败
							isSuccess = false;
							break; // 跳出循环
						}
					}

					// 删除该汇报
					if (isSuccess) {
						sql = "delete from taskreport where t_reportid = "
								+ delid;
						int n = OperateDB.update(sql);
						if (n == 0) {// 删除失败
							isSuccess = false;
							break; // 跳出循环
						}
					}

				} else {// 不存在汇报
					isSuccess = false;
					break; // 跳出循环
				}

			}
		}
		return isSuccess;
	}

	/*
	 * 删除指定id的项目
	 */
	public static boolean delProject(String[] delID) {
		boolean isSuccess = true;
		if (delID.length < 1) {
			isSuccess = false;
		} else {
			Connection conn = ConnectDB.getConnection();
			boolean autoCommit;
			try {
				autoCommit = conn.getAutoCommit();
				conn.setAutoCommit(false);// 取消自动提交

				for (int i = 0; i < delID.length; i++) {
					int taskid = Integer.parseInt(delID[i]);

					String sql = "select * from taskinfo where taskid = "
							+ taskid;
					// System.out.println(sql);
					List<Map> list = new ArrayList<Map>();
					list = OperateDB.executeQuery(sql);
					if (list.size() > 0) {// 存在该项目
						Map map = list.get(0);

						// 删除groupmember,groupinfo中的信息
						sql = "select groupid from groupinfo where group_type = '项目组'"
								+ " and projectid = " + taskid;
						System.out.println(sql);
						List<Map> glist = new ArrayList<Map>();
						glist = OperateDB.executeQuery(sql);
						if (glist.size() > 0) {// 存在该项目组
							Map gmap = glist.get(0);
							int groupid = (Integer) gmap.get("groupid");
							// 删除项目组的成员
							sql = "delete from groupmember where groupid = "
									+ groupid;
							// System.out.println(sql);
							int n = OperateDB.update(sql);
							if (n == 0) {// 删除成员失败
								isSuccess = false;
								break; // 跳出循环
							} else {// 删除groupinfo中的信息
								sql = "delete from groupinfo where groupid = "
										+ groupid;
								// System.out.println(sql);
								n = OperateDB.update(sql);
								if (n == 0) {
									isSuccess = false;
									break; // 跳出循环
								}
							}

						} else {// 不存在该项目组
							isSuccess = false;
							break; // 跳出循环
						}

						// 删除taskreport中的信息
						sql = "select * from taskreport where taskid = "
								+ taskid;
						// System.out.println(sql);
						List<Map> tlist = new ArrayList<Map>();
						tlist = OperateDB.executeQuery(sql);
						if (tlist.size() > 0) {// 该项目组有项目汇报
							int size = tlist.size();
							String[] reportID = new String[size];
							// 将该项目的所有项目汇报的id放在数组reportID中
							for (int j = 0; j < size; j++) {
								Map tmap = tlist.get(j);
								String reportid = tmap
										.get("t_reportid").toString();
								reportID[j] = reportid;
								// System.out.println(reportID[j]);
							}
							if (!Task.delReport(reportID)) {// 删除taskreport中的信息成功
								isSuccess = false;
								break; // 跳出循环
							}

						}

						// 删除appendinfo中的信息
						String isAppend = map.get("isappend").toString();
						if (isAppend.equals("1")) {// 有附件,则删除附件
							sql = "delete from appendinfo where append_type = '项目任务'"
									+ "and fileid = " + taskid;
							// System.out.println(sql);
							int n = OperateDB.update(sql);
							if (n == 0) {// 删除附件失败
								isSuccess = false;
								break; // 跳出循环
							}
						}

						// 删除taskinfo中的信息// 删除该项目汇报
						sql = "delete from taskinfo where taskid = " + taskid;
						// System.out.println(sql);
						int n = OperateDB.update(sql);
						if (n == 0) {// 删除失败
							isSuccess = false;
							break; // 跳出循环
						}

					} else {// 不存在项目
						isSuccess = false;
						break; // 跳出循环
					}
				}

				if (isSuccess != false) {
					conn.commit();// 手动提交
				} else {
					conn.rollback();
				}
				conn.setAutoCommit(autoCommit);// 恢复现场
			} catch (SQLException e) {
				e.printStackTrace();
				isSuccess = false;
			} catch (NumberFormatException e) {
				e.printStackTrace();
				isSuccess = false;
			} finally {
				OperateDB.close(conn);
			}
		}

		return isSuccess;
	}

	/*
	 * 获得组长权限下能够看到的所有项目 
	 * param:组长的编号
	 */
	public static List<Project> getLProject(String userno, String condition,
			String condition2) {
		String append = "";
		if (condition.trim().equals("finish")) {
			append += " and status = '1'";
		} else if (condition.trim().equals("notfinish")) {
			append += " and status = '0'";
		} else {
			append += "";
		}

		if (condition2.trim().equals("headman")) {
			append += " and leaderid = '" + userno + "'" + " and level = 3";
		} else if (condition2.trim().equals("member")) {
			append += " and ((leaderid != gm.memberid) or (leaderid = gm.memberid and level = 2))";
		} else {
			append += "";
		}

		String sql = "select ti.* from groupinfo as gi, groupmember as gm, taskinfo as ti "
				+ " where gi.groupid = gm.groupid "
				+ " and gm.memberid = '"
				+ userno + "' " + " and ti.taskid = gi.projectid " + append + " order by ti.taskid desc ";
		// System.out.println(sql);
		List<Project> plist = null;
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化plist对象
			plist = new ArrayList<Project>();
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				Project p = new Project();
				p.init(map);
				plist.add(p);
			}
		}
		return plist;
	}

	public static List<Project> getSProject(String userno, String condition) {
		String append = "";
		if (condition.trim().equals("finish")) {
			append += " and status = '1'";
		} else if (condition.trim().equals("notfinish")) {
			append += " and status = '0'";
		} else {
			append += "";
		}

		String sql = "select ti.* from groupinfo as gi, groupmember as gm, taskinfo as ti "
				+ " where gi.groupid = gm.groupid "
				+ " and gm.memberid = '"
				+ userno
				+ "' "
				+ " and ((leaderid != gm.memberid) or (leaderid = gm.memberid and level = 2))"
				+ " and ti.taskid = gi.projectid " + append + " order by ti.taskid desc ";
		// System.out.println(sql);
		List<Project> plist = null;
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化plist对象
			plist = new ArrayList<Project>();
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				Project p = new Project();
				p.init(map);
				plist.add(p);
			}
		}
		return plist;
	}

	// 获得指定taskid的项目任务
	public static List<Project> getProject(int taskid) {
		String sql = "select * from taskinfo where taskid = " + taskid;
		// System.out.println(sql);
		List<Project> plist = null;
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化plist对象
			plist = new ArrayList<Project>();
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				Project p = new Project();
				p.init(map);
				plist.add(p);
			}
		}
		return plist;
	}

	// 获得指定taskid的个人任务?
	public static List<PTask> getTask(int taskid) {
		String sql = "select * from taskinfo where taskid = " + taskid;
		// System.out.println(sql);
		List<PTask> tlist = null;
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化tlist对象
			tlist = new ArrayList<PTask>();
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PTask pt = new PTask();
				pt.init(map);
				tlist.add(pt);
			}
		}
		return tlist;
	}

	public static boolean modifyGrade(int proID, int grade, int newGrade) {
		boolean isSuccess = true;
		if (newGrade == 2 || newGrade == 3) {
			if (newGrade != grade) {
				String sql = "update taskinfo set level = '" + newGrade + "' "
						+ " where taskid = " + proID;
				int n = OperateDB.update(sql);
				if (n <= 0) {
					isSuccess = false;
				}
			}
		} else {
			isSuccess = false;
		}
		return isSuccess;
	}

	public static boolean modifyTime(int proID, String endTime, String newTime) {
		boolean isSuccess = true;
		// System.out.println("::::" + newTime);
		if (!newTime.trim().equals("")) {
			if (!newTime.equals(endTime)) {
				String sql = "update taskinfo set endtime = '" + newTime + "' "
						+ " where taskid = " + proID;
				int n = OperateDB.update(sql);
				if (n <= 0) {
					isSuccess = false;
				}
			}
		} else {
			isSuccess = false;
		}
		// System.out.println(isSuccess);
		return isSuccess;
	}

	public static boolean modifyLeader(int groupID, String leaderID,
			String newLeader, String[] member) {
		// System.out.println(newLeader);
		boolean isSuccess = true;
		if (!newLeader.trim().equals("")) {// 修改为空
			if (!newLeader.trim().equals(leaderID)) {// 与原来的leaderid相同则不做修改
				String sql = "update groupinfo set leaderid = '" + newLeader
						+ "' " + " where groupid = " + groupID;
				int n = OperateDB.update(sql);
				if (n <= 0) {
					isSuccess = false;
				}

				if (!Task.isOld(newLeader, member)) {// 新更改的leaderdID是新增加的成员
					sql = "insert into groupmember values(?, ?)";
					List<Object> gmlist = new ArrayList<Object>();
					gmlist.add(groupID);
					gmlist.add(newLeader);
					OperateDB.setList(gmlist);
					if (!OperateDB.insert(sql)) {
						isSuccess = false;
					}
				}
			}

		} else {
			isSuccess = false;
		}
		// System.out.println(isSuccess);
		return isSuccess;
	}

	public static boolean modifyMemeber(int groupID, String leaderID,
			String[] member, String[] newMember, String newLeader) {
		boolean isSuccess = true;
		if (newMember != null && newMember.length != 0) {
			for (int i = 0; i < newMember.length; i++) {
				String memberID = newMember[i];
				if (!memberID.equals(leaderID) && !Task.isOld(memberID, member)) {// 新更改的成员ID与组长不同，如果相同则不更新数据库
																					// 新更改的成员是否已经是改组成员，如果是则不更新数据库
					String sql = "insert into groupmember values(?, ?)";
					List<Object> gmlist = new ArrayList<Object>();
					gmlist.add(groupID);
					gmlist.add(memberID);
					// System.out.println(memberID);
					OperateDB.setList(gmlist);
					if (!OperateDB.insert(sql)) {
						isSuccess = false;
						break;
					}
				}
			}
		} else {
			isSuccess = false;
		}

		if (member != null && member.length != 0) {
			// System.out.println(leaderID + ":" + newLeader);
			for (int i = 0; i < member.length; i++) {
				String oldMember = member[i];
				// System.out.println(oldMember);
				if (!oldMember.equals(newLeader)) {// 旧成员非新组长
					if (!Task.isInNew(oldMember, newMember)) {// 原来的成员是否在新成员中，如果不在则删除在数据库中的信息，否则不更新数据库
						String sql = "delete from groupmember where memberid= "
								+ oldMember + " and groupid = " + groupID;
						int n = OperateDB.update(sql);
						if (n <= 0) {
							isSuccess = false;
							break;
						}
					}
				}
			}
		} else {
			isSuccess = false;
		}

		// System.out.println(isSuccess);
		return isSuccess;
	}

	public static boolean isOld(String memberID, String[] member) {
		boolean isOld = false;
		// System.out.println(memberID);
		for (int i = 0; i < member.length; i++) {
			String oldMember = member[i];
			// System.out.println(oldMember);
			if (memberID.equals(oldMember)) {
				isOld = true;
				break;
			}
		}
		// System.out.println(isOld);
		return isOld;
	}

	public static boolean isInNew(String oldMember, String[] newMember) {
		boolean isInNew = false;
		for (int j = 0; j < newMember.length; j++) {
			if (oldMember.equals(newMember[j])) {
				isInNew = true;
				break;
			}
		}
		// System.out.println(isInNew);
		return isInNew;
	}

	public static List<PTask> getSTask(String userno, int proID) {
		String sql = "select * from taskinfo where toid = '" + userno + "' "
				+ " and taskname = '" + proID + "' and task_type = '个人任务'";
		// System.out.println(sql);
		List<PTask> tlist = null;
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化plist对象
			tlist = new ArrayList<PTask>();
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PTask pt = new PTask();
				pt.init(map);
				tlist.add(pt);
			}
		}
		return tlist;
	}

	public static List<PTask> getSTask(String userno, int proID, int startPos,
			int pageSize) {
		String sql = "select * from taskinfo where toid = '" + userno + "' "
				+ " and taskname = '" + proID + "' and task_type = '个人任务'"
				+ " order by starttime desc limit " + startPos + ", "
				+ pageSize;
		// System.out.println(sql);
		List<PTask> tlist = null;
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化plist对象
			tlist = new ArrayList<PTask>();
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PTask pt = new PTask();
				pt.init(map);
				tlist.add(pt);
			}
		}
		return tlist;
	}

	// 获得指定老师或者组长给组员的个人任务
	public static List<PTask> getGTask(String userno, int proID) {
		String sql = "select * from taskinfo where fromid = '" + userno + "' "
				+ " and taskname = '" + proID + "' and task_type = '个人任务'";
		// System.out.println(sql);
		List<PTask> tlist = null;
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化plist对象
			tlist = new ArrayList<PTask>();
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PTask pt = new PTask();
				pt.init(map);
				tlist.add(pt);
			}
		}
		return tlist;
	}

	// 获得指定老师或者组长给组员的指定条数的个人任务
	public static List<PTask> getGTask(String userno, int proID, int startPos,
			int pageSize) {
		String sql = "select * from taskinfo where fromid = '" + userno + "' "
				+ " and taskname = '" + proID + "' and task_type = '个人任务' "
				+ " order by starttime desc limit " + startPos + ", "
				+ pageSize;
		// System.out.println(sql);
		List<PTask> tlist = null;
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化plist对象
			tlist = new ArrayList<PTask>();
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PTask pt = new PTask();
				pt.init(map);
				tlist.add(pt);
			}
		}
		return tlist;
	}

	// 获得组长发布给组员的个人任务，与上方法同。
	public static List<PTask> getLTask(String userno, int proID) {
		String sql = "select * from taskinfo where fromid = '" + userno + "' "
				+ " and taskname = '" + proID + "' and task_type = '个人任务'";
		// System.out.println(sql);
		List<PTask> tlist = null;
		List<Map> list = new ArrayList<Map>();

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有项目时才初始化plist对象
			tlist = new ArrayList<PTask>();
			for (int i = 0; i < list.size(); i++) {
				Map map = list.get(i);
				PTask pt = new PTask();
				pt.init(map);
				tlist.add(pt);
			}
		}
		return tlist;
	}

	public static boolean changeProgress(int proID, String nowStatus) {
		boolean isSuccess = true;

		String sql = "update taskinfo set status = '" + nowStatus
				+ "' where taskid = " + proID;
		// System.out.println(sql);
		int n = OperateDB.update(sql);
		if (n <= 0) {
			isSuccess = false;
		}

		return isSuccess;
	}

}
